/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.MtpkgPackageDetailDao;
import com.elcom.emocbc.dbmapping.tables.pojos.MtpkgPackageDetail;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class MtpkgPackageDetailPersistance extends MtpkgPackageDetailDao {

    public MtpkgPackageDetailPersistance() {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<MtpkgPackageDetail> getPackageDetailsWithPrefix(String prefix) throws SQLException {
        ResultSetHandler<List<MtpkgPackageDetail>> resultSetHandler = new ResultSetHandler<List<MtpkgPackageDetail>>() {
            @Override
            public List<MtpkgPackageDetail> handle(ResultSet rs) throws SQLException {
                List<MtpkgPackageDetail> result = new ArrayList<MtpkgPackageDetail>();
                while (rs.next()) {
                    int i = 1;
                    MtpkgPackageDetail packageDetail = new MtpkgPackageDetail();
                    packageDetail.setPkgdId(rs.getString(i++));
                    packageDetail.setPkgdName(rs.getString(i++));
                    packageDetail.setPkgdType(rs.getString(i++));
                    packageDetail.setPkgId(rs.getInt(i++));
                    packageDetail.setRegisterCode(rs.getString(i++));
                    packageDetail.setDeregisterCode(rs.getString(i++));
                    packageDetail.setActiveFee(rs.getBigDecimal(i++));
                    packageDetail.setRecurrentFee(rs.getBigDecimal(i++));

                    result.add(packageDetail);
                }
                return result;
            }
        };
        Connection con = DbAccess.getInstance().getConnection();

        QueryRunner qr = new QueryRunner();
        String dailyPkgId = prefix;
        String weeklyPkgId = prefix + "1";
        String monthlyPkgId = prefix + "2";
        String sql = "SELECT * "
                + "FROM mtpkg_package_detail "
                + "WHERE pkgd_id in (?,?,?) ";
        try {
            return qr.query(con, sql, resultSetHandler, dailyPkgId, weeklyPkgId, monthlyPkgId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }
}
